﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Generator.DBUtility
{
    public class SQLServerUtility
    {
        public static DataSet GetTableNames(string ConnectStr)
        {
            DBConnectionConfig cfg = new DBConnectionConfig(ConnectStr);
            string SQL = string.Format("select tbls.name from {0}.sys.tables as tbls order by name;", cfg.DBName);
            List<string> tables = new List<string>();

            DataSet dtSet = new DataSet();
            using (SqlConnection con = new SqlConnection(ConnectStr))
            {

                SqlCommand command = new SqlCommand(SQL, con);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dtSet);
            }
            return dtSet;


        }

        public static DataSet GetTableFields(string ConnectStr, string tableName)
        {

            DBConnectionConfig cfg = new DBConnectionConfig(ConnectStr);
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(string.Format(" select  cols.name as FieldName, ", ""));
            sb.AppendLine(string.Format(" tps.name  as TypeName,  ", ""));
            sb.AppendLine(string.Format(" cols.max_length, ", ""));
            sb.AppendLine(string.Format(" cols.is_nullable as Nullable, ", ""));
            sb.AppendLine(string.Format(" cols.is_identity ", ""));
            sb.AppendLine(string.Format(" from {0}.sys.tables as tbls inner join {0}.sys.columns as cols ", cfg.DBName));
            sb.AppendLine(string.Format(" on tbls.object_id = cols.object_id ", ""));
            sb.AppendLine(string.Format(" inner join Sys.Types  tps  ", ""));
            sb.AppendLine(string.Format(" on cols.user_type_id=tps.user_type_id ", ""));
            sb.AppendLine(string.Format(" where tbls.name = '{0}' ", tableName));

            DataSet dtSet = new DataSet();
            using (SqlConnection con = new SqlConnection(ConnectStr))
            {
                SqlCommand command = new SqlCommand(sb.ToString() , con);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dtSet);
            }
            return dtSet;
        }

        public static string GetPrimaryKey(string ConnectStr, string tableName)
        {

            DBConnectionConfig cfg = new DBConnectionConfig(ConnectStr);
            StringBuilder sb = new StringBuilder();

            sb.AppendLine(string.Format(" select index_col ('[{0}]', index_id, 1) AS KeyField from sys.indexes indx ", tableName));
            sb.AppendLine(string.Format(" inner join sys.objects objs on objs.object_id=indx.object_id ", ""));
            sb.AppendLine(string.Format(" where objs.name='{0}' ", tableName));
            sb.AppendLine(string.Format("  and is_primary_key=1 ", ""));


            DataSet dtSet = new DataSet();
            using (SqlConnection con = new SqlConnection(ConnectStr))
            {
                SqlCommand command = new SqlCommand( sb.ToString() , con);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dtSet);
            }

            string reValue = "";
            if (dtSet.Tables.Count > 0 && dtSet.Tables[0].Rows.Count > 0 && dtSet.Tables[0].Rows[0]["KeyField"] != null)
            {
                reValue = string.Format("{0}", dtSet.Tables[0].Rows[0]["KeyField"]);
            }

            return reValue;
        }



        class DBConnectionConfig
        {
            public string DBName { get; private set; }

            public DBConnectionConfig(string ConnectStr)
            {
                string[] configs = ConnectStr.Split(new char[] { ';' });
                foreach (string sec in configs)
                {
                    string[] s = sec.Split(new char[] { '=' });
                    if (s.Length == 2)
                    {
                        if (s[0].ToLower().CompareTo("Initial Catalog") == 0)
                        {
                            DBName = s[1];
                        }
                    }
                }
            }
        }

    }
}
